package src.queries;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import  src.entities.User;



public class UserManagementQueries extends UserQueries{

	public UserManagementQueries(Connection c, SecurityQueries security) {
		super(c, security);
	}

	public User retrieveUserData(String userName) throws SQLException{
		
		PreparedStatement p1 = makePreparedStatement(
				"Select * FROM users WHERE user_name = ?" );

		p1.setString(1, userName);
		ResultSet rs1 = p1.executeQuery();
		rs1.next();
		
		PreparedStatement p2 = makePreparedStatement(
				"Select * FROM persons WHERE user_name = ?");
		
		p2.setString(1, userName);
		ResultSet rs2 = p2.executeQuery();
		rs2.next();
		
		User user = new User(userName, rs1.getString("password"), 
				rs1.getString("class"), rs1.getDate("date_registered"),
				rs2.getString("first_name"), rs2.getString("last_name"),
				rs2.getString("address"), rs2.getString("email"), rs2.getString("phone"));
		
		return user;
	}
	
	public boolean checkEmail(String email) throws SQLException {
		PreparedStatement p1 = makePreparedStatement(
				"Select count(*) FROM persons WHERE email = ?");
		
		p1.setString(1, email);
		
		ResultSet rs = p1.executeQuery();
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
		}
		
		//If count is 0, the email doesn't exist.
		if (count == 0)
			return true;
		
		//Otherwise the email does exist so we cannot accept it.
		else
			return false;
	}

	public void createUser(User user) throws SQLException{
		PreparedStatement p1 = makePreparedStatement(
		"INSERT INTO users VALUES (?,?,?,?)" );

		p1.setString(1, user.getUserName());
		p1.setString(2, user.getPassword());
		p1.setString(3, user.getUserClass());
		p1.setDate(4, parseDate(user.getDateRegistered()));

		PreparedStatement p2 = makePreparedStatement(
				"INSERT INTO persons VALUES (?, ?, ?, ?, ?, ?)");

		p2.setString(1, user.getUserName());
		p2.setString(2, user.getFirstName());
		p2.setString(3, user.getLastName());
		p2.setString(4, user.getAddress());
		p2.setString(5, user.getEmail());
		p2.setString(6, user.getPhone());

		p1.executeUpdate();
		p2.executeUpdate();
		
		PreparedStatement pcom = makePreparedStatement("commit");
		pcom.executeUpdate();
	}

	public void deleteUser(String userName) throws SQLException{
		PreparedStatement p1 = makePreparedStatement(
				"DELETE FROM persons WHERE user_name = ?" );
		
		p1.setString(1, userName);
		p1.executeUpdate();
		
		PreparedStatement p2 = makePreparedStatement(
				"DELETE FROM users WHERE user_name = ?");
		p2.setString(1, userName);
		p2.executeUpdate();
		
		PreparedStatement pcom = makePreparedStatement("commit");
		pcom.executeUpdate();
		
	}
	public ArrayList<String> retrieveDoctorNames(String patientName) throws SQLException{
		PreparedStatement p = makePreparedStatement(
		"SELECT doctor_name FROM family_doctor WHERE patient_name = ?" );

		p.setString(1, patientName);
		ResultSet rs = p.executeQuery();
		
		ArrayList<String> doctorNames = new ArrayList<String>();
		
		while(rs.next()) {
			doctorNames.add(rs.getString("doctor_name"));
		}
		return doctorNames;
	}
	public void removeDoctorFromPatient(String patientName, 
			String doctorName) throws SQLException{
		PreparedStatement p = makePreparedStatement(
		"DELETE FROM family_doctor WHERE patient_name = ?" +
		"AND doctor_name = ?" );

		p.setString(1, patientName);
		p.setString(2, doctorName);
		p.executeUpdate();
		
		PreparedStatement pcom = makePreparedStatement("commit");
		pcom.executeUpdate();
	}
	public void addDoctorToPatient(String patientName, String doctorName) throws SQLException{
		if(security.isDoctor(doctorName)){
			PreparedStatement p = makePreparedStatement(
			"INSERT INTO family_doctor VALUES (?,?)" );
	
			p.setString(1, doctorName);
			p.setString(2, patientName);
			p.executeUpdate();
			
			PreparedStatement pcom = makePreparedStatement("commit");
			pcom.executeUpdate();
		}
	}
	
	public ArrayList<String> getAllOfClass(String type) throws SQLException {
		PreparedStatement p = null;
		
		if (type.equals("all")) {
			p = makePreparedStatement("SELECT user_name FROM users");
		}
		else {
			p = makePreparedStatement("SELECT user_name FROM users WHERE class = ?" );
			p.setString(1, type);
			
			System.out.println(p);
		}

		ResultSet rs = p.executeQuery();
		
		ArrayList<String> users = new ArrayList<String>();
		
		while(rs.next()) {
			users.add(rs.getString("user_name"));
		}
		return users;
	}
	
	public ArrayList<User> getUserList() throws SQLException {
		PreparedStatement p = makePreparedStatement(
				"SELECT p.user_name, p.first_name, last_name, p.address, p.email, p.phone, u.password, u.class, u.date_registered " +
				"FROM persons p, users u " +
				"WHERE p.user_name = u.user_name");
		ResultSet rs = p.executeQuery();
		
		ArrayList<User> users = new ArrayList<User>();
		
		while(rs.next()) {
			users.add(new User(
						rs.getString("user_name"), 
						rs.getString("password"), 
						rs.getString("class"), 
						rs.getDate("date_registered"),
						rs.getString("first_name"), 
						rs.getString("last_name"),
						rs.getString("address"), 
						rs.getString("email"), 
						rs.getString("phone")
					));
		}
		
		return users;
	}
}
